home
***
CD-ROM
|
disk
|
FTP
|
other
***
search
/
BMUG Revelations
/
BMUG Revelations.toast
/
Business
/
Excel
/
Excel Calender
/
Read Me
< prev
Wrap
Text File
|
1987-01-25
|
10KB
|
188 lines
The Excel Calendar-Book Publishing System
MOTIVATION
When I first got my Macintosh, one of its principal uses was to help me
organize and manage my time. Since I did a lot of work on a contract basis, I
really needed to keep accurate records of how much time I spent doing what for
whom. I had a great system worked out using Microsoft File and ThinkTank, and
since I had a Mac at the office as well as home, it was an entirely on-line
setup - no need for a paper calendar or appointment book or anything.
A few months ago, however, I started a new job with a real consulting firm, and
now my need to track time this way is even more important than before; and I no
longer have access to a Mac at the office! I had to come up with away to keep
accurate records that was easy to maintain, portable, and (gulp) paper-based.
The first idea that I got was a very common solution - use one of those fancy
appointment books made for consultants, lawyers, etc., that has a full page for
each day. These pages have special sections for setting appointements and
planning each day ahead of time, and also for recording activities and progress
as each day passes - sounded perfect tome.
On investigating this idea, however, I found out that the people making these
books have a real racket going! The cheapest one I could find that fit my bill
was over $45.00! Not a chance, says I, not while I still have a Mac and
Laserwriter at home!
I realized that I should be able to design my own set of pages, and set up my
Mac to make a calendar book for me, just the way I want it. And that’s what I
did. And that’s what this is. I hope it proves useful to at least a few.
INTRODUCTION
The system’s principal function is to simply print a set of pages that can be
put in a ring binder and used to keep track of things to do and appointments;
remind you of recurring dates like birthdays, anniversaries, etc.; and record
actual work completed and assign charge-back codes to each activity. It allows
you to maintain a list of all recurring dates that you want to print on your
calendar, and will automatically stick them on the page when that date is
printed. The calendar is printed in weekly “batches", and the system prints
the calendar on both sides of the paper. Each day has its own page with
sections for a to-do list, notes & memos", and appointments & tasks; the to-do
section is used for a-priori planning, and the "Tasks” section for recording
actual progress. Tasks & appointments can be recorded in 15 minute intervals,
from 8:00 am to 6:00 pm. A page is printed at the start of each week for
listing things you need to plan for that week; a similar page can be printed
for each month as well.
I use the system this way: I first print out the full year’s monthly planning
pages, and then I print out the weekly “batches” for the next two months. When
the first of these months is past, I print out the third month’s batch. As
events come up, I record them on the weekly planning page if they fall in the
next month or two; if not, I put them on the “monthly” page. At the start of
the week, I transfer items from the weekly page to the to-do section of the
appropriate date page. As the week progresses, I check things accomplished off
in the to-do section, recording the appropriate billing info on the right side
of each day's page. Things that I do not complete get transferred to the to-do
section of the next day. All of this will make a lot more sense after you
print your first set of pages; if you have ever used one of the commercially
available calendar books like this, it will all be very familiarto you.
Since the actual layout of each calendar page is nothing but a formatted Excel
worksheet, it can be modified to suit your specific whims; just be sure to pay
attention to the current page layout and named regions, so you don’t have any
trouble with the macros. It should also be very easy to create new pages to
supplement your calendar book, and add them to the macro system (the macro
programming is not too sophisticated, but feel free to ask me if you need any
insights).
NOTE: This version of the application only works with a Laserwriter! It could
be modified to work with pin-fed paper on an imagewriter -- in fact, an earlier
version of it did support both printers, but I cut the IW support out simply
because this kind of printing on an Imagewriter would take too long to be
practically useful.
GETTING STARTED
The system uses a rather nice macro-driven interactive front-end that directs
you through everything, including the printing and changing the paper in the
printer. In order to get to this menu and make sure that it works properly,
you need to set up the system files right when you start using it. After
unpacking the XLCAL.P3T file (which you obviously have done if you are reading
this!), you should have the following files:
File Name Type
========= ====
Pages Excel Worksheet
Datelist Excel Worksheet
Calendar System Excel Macro document
Calendars… Excel “Resume” document
The “Calendar” worksheet contains the pages for the weekly batches of the
calendar book, as well as the monthly planning pages; “Datelist" contains a
database for maintaining recurring items to be printed on the daily pages; and
“Calendar System” contains the macros to control the system operation.
Obviously, it is important that none of these files be renamed or deleted, or
else the calendar system will not function properly. The “Calendars…” document
is a renamed copy of the “Resume Excel” document thatExcel automatically
maintains; by opening this file, Excel will automatically open each of the
others, leaving you ready to run.
RUNNING THE MACRO APPLICATION
When you have all 3 files open in Excel, simply press Command-Option-m to
invoke the mini-menu that drives the application. The menu is simply a dialog
box that presents you with this message:
“Enter Option: Print [D]aily Pages, Print [M]onthly Pages, Print Date [L]ist”
In order to select one of these three options, type the letter that is enclosed
in square brackets: “D” for “Print Daily Pages”, “M” for “Print Monthly
Pages”, or “L” for “Print Date List”. You can enter the letter in upper or
lower case, and if you enter an invalid letter, it macro will ask again.
An important function that isn’t on the menu is the editing and customization
of the contents of the DateList file. This one you can do on your own, using
standard Excel editing techniques. The file I included contains all recognized
holidays for 1987, but you will want to add entries for your list of birthdays,
anniversaries, company meetings & events, etc. Do this before printing
anything.
PRINTING DAILY PAGES
This is the big one - the option that does the real work of the system. When
you select this option, the system first asks you for a “Starting Date”, and it
expects you to enter the date of the Monday for the first week you want to
print. The system thinks of weeks as running from Monday to the following
Sunday, and thats how they are organized in the calendar printout (It was
defined this way simply because thats the way I like it!). If you enter a date
for any day other than a Monday, the system will get annoyed, beep, and ask you
to try again until it get what it wants.
Next, it asks you for the number of weeks you want to print in one shot. What
happens is this: the system will print out all the right-hand-side pages for
all the weeks you specify, and then stop, asking you to reverse the pages
through the printer. You simply take the stack of paper from the laserwriter’s
spit-out tray and reload the paper cassette with these pages, head-first (the
top of the page goes under the little metal clamp) and face-up. It will then
print all the left-hand-side pages on the back of the right-hand-side pages, so
that the pages can be punched and put into the ring binder perfectly arranged.
Before it prints each week, however, the system will go to the DateList file
and look for items to include on the page for each date.
Because there is no mechanism for controlling some Print command dialog items
from within a macro, you will need to check the current settings of the print-
quality and “preview” option before executing this option; simply issue the
Print command. If the dialog settings are the ones you want to use, just click
“Cancel” and go ahead and select the appropriate print option from the
mini-menu. If not, change them to what you want, and click “OK”; cancel the
ensuing print as soon as possible with COMMAND-PERIOD, and then execute the
menu option.
PRINTING MONTHLY PLANNING PAGES
This option simply prints the twelve monthly planning pages that can be used to
maintain a-priori to-do lists for each month. I use a colored, card-stock
paper for these pages, so that they act as nice dividers for the ring-binder.
PRINTING THE DATE LIST FILE
This option prints the DateList file contents, setting appropriate print area &
titles first. Unlike the other options, this one presents the user with the
Print command dialog box, so that special print options (such as a page range)
may be specified.
FINAL COMMENTS
I have found this application very useful, as have several of my friends. Feel
free to pass it around as you wish, but please include all the files with any
copies you make. If it helps you to save or make any money, please reward my
efforts by sending me $10.00 (or whatever you think it is worth). If you are
just examining the macro code ’cause you write Excel applications yourself,
sharing your best efforts with me will be reward enough. In any event, I
encourage your comments and criticism.
Bob Pulgino
1420 “N” Street, NW, #414
Washington, DC 20005
202/797-0879
CI$ 76174,3377
December 26, 1986